package com.jdbc;

import java.sql.Connection;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import javax.sql.DataSource;

import org.junit.Test;
import org.springframework.context.ApplicationContext;
import org.springframework.context.support.ClassPathXmlApplicationContext;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;

import com.mchange.v2.c3p0.ComboPooledDataSource;

public class TestJdbc {
	private ApplicationContext applicationContext = new ClassPathXmlApplicationContext("applicationContext_jdbc.xml");

	private JdbcTemplate jdbcTemplate = null;
	{
		jdbcTemplate = (JdbcTemplate) applicationContext.getBean("jdbcTemplate");
	}

	/**
	 * 使用数据库函数
	 */
	@Test
	public void tesetQueryForList2() {
		String sql = "select count(*) from user ";
		// RowMapper列映射,用于数据库和一个对象的类型映射起来。RowMapper是接口,一般使用其子类BeanPropertyRowMapper.
		Long queryForObject = jdbcTemplate.queryForObject(sql, Long.class);
		System.out.println(queryForObject);
	}

	/**
	 * 查询实体类的集合
	 */
	@Test
	public void tesetQueryForList() {
		String sql = "select * from user where id < ?";
		RowMapper<User> rowMapper = new BeanPropertyRowMapper<>(User.class);
		List<User> query = jdbcTemplate.query(sql, rowMapper, 6);
		System.out.println(query);
	}

	/**
	 * 从数据库中获取一个条数据,实际得到对应的一个对象.
	 * JdbcTemplate不支持级联属性.说到底jdbcTemplate只是一个小工具,用于简化操作数据库,而不是框架.
	 */
	@Test
	public void testqueryForObject() {
		String sql = "select * from user where id=2";
		// RowMapper列映射,用于数据库和一个对象的类型映射起来。RowMapper是接口,一般使用其子类BeanPropertyRowMapper.
		RowMapper rowMapper = new BeanPropertyRowMapper<>(User.class);
		User user = (User) jdbcTemplate.queryForObject(sql, rowMapper);
		System.out.println(user);
	}

	/**
	 * 批处理: insert into user(uname,pwd,sex) values(?,?,?)
	 */
	@Test
	public void testBatchUpdate() {

		String sql = "insert into user(uname,pwd,sex) values(?,?,?)";
		List<Object[]> batchArgs = new ArrayList<>();

		batchArgs.add(new Object[] { "wwj3", 123, 1 });
		batchArgs.add(new Object[] { "wwj4", 123, 1 });

		jdbcTemplate.batchUpdate(sql, batchArgs);
	}

	/**
	 * 执行修改操作
	 */
	@Test
	public void TestUpdate() {
		// jdbcTemplate = (JdbcTemplate) applicationContext.getBean("jdbcTemplate");
		String sql = "update user set uname=? where id=?";
		jdbcTemplate.update(sql, "wwj2", 7);

	}

	@Test
	public void testDataSource() {
		DataSource bean = applicationContext.getBean(DataSource.class);
		ComboPooledDataSource bean2 = (ComboPooledDataSource) applicationContext.getBean("dataSource");
		try {
			Connection connection = bean.getConnection();
			Connection connection2 = bean2.getConnection();
			System.out.println(connection);
			System.out.println(connection2);
		} catch (SQLException e1) {

		}
	}
}
